package edu.syr.iis.web.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.mysql.jdbc.ResultSet;

public class SQLService {

	private transient static Log log = LogFactory.getLog(SQLService.class);

	String url;
	String user;
	String password;

	Connection connection;

	public SQLService()
	{
		user = "root";
		password = "Fk02Bn";
		url = "jdbc:mysql://localhost:3306/adsanka_conversation" +
		"?user=" + user +
		"&password=" + password;
		try
		{
			// Step 1: Load the JDBC driver.
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			// Step 2: Establish the connection to the database.
			connection = DriverManager.getConnection(url);
		}
		catch (Exception e)
		{
			log.info("Got an exception! ", e);
		}
	}

	public void insertConversation(ConversationModel conversation)
	{
		if (conversation.id.equals(""))
		{
			System.out.println("ERROR: No ID is set!");
			return;
		} else
		{
			System.out.println("Inserting ID:" + conversation.id);
		}
		
		Date date = new Date("1/1/2001 00:00:00");
		
		if (!conversation.serviceTime.equals("") && ! conversation.serviceTime.equals("0"))
			date = new Date(conversation.serviceTime);
		else if (!conversation.queueTime.equals("") && ! conversation.queueTime.equals("0"))
			date = new Date(conversation.queueTime);
		
		String datasetId = "2"; // hard coded this time
		String month = date.month;
		String year = date.year;
		String dateTimeEntered = date.toString();
		
		//		String recordId = "0"; // not used anyway
		//		String institutionId = "0"; // not in the data
		
		String questionId = "2000" + conversation.id; 
		String assignedTo = conversation.libraryName;
		
		String sessionTime = conversation.serviceDuration;
		String waitTime = conversation.queueDuration;
			
			
		String stmt = "INSERT into conversation " + 
		" (datasetId, month, year, questionId, assignedTo, dateTimeEntered, sessionTime, waitTime) " +
		"VALUES ( " + datasetId + ", "+ month + ", " + year + ", " + questionId + ", \'" + assignedTo + "\', \'"  + dateTimeEntered + "\', \'" + sessionTime + "\', \'" + waitTime + "\' );";
		
		if (connection == null)
		{
			log.info("Connection failed.");
		}
		try 
		{
			connection.createStatement().execute(stmt);
		}
		catch (Exception e)
		{
			log.info("Got an exception with " + stmt, e);
		}
	}

	public String getConversationIdFromQuestionId(String questionId)
	{
		String stmt= 	    
		    "SELECT conversationId FROM Conversation WHERE questionID = " + questionId; 

		ResultSet rs = null;

		if (connection == null)
		{
		    log.info("Connection failed.");
		}

		try 
		{
		    rs = (ResultSet) connection.createStatement().executeQuery(stmt);	    
		    if (rs == null) 
		    {
				log.info("Query failed.");
		    }
		    else if (rs.next())
			{
				return Integer.toString(rs.getInt(1));
			}
			else
			{
				log.info("Conversation with questionId:" + questionId + " was not found!");
			}				
		}
		catch (SQLException e)
		{
		    if (rs == null) {
			log.info("rs is null.");
		    }
		}
		return null;
	}
	

	public void insertMessage(String questionId, String sequence, String speaker, String content) 
	{
		if (questionId.equals("") || questionId.equals("NA"))
		{
			System.out.println("ERROR: No ID is set!");
			return;
		} 
		
		String speakerInt;
		if (speaker.equals("L"))
				speakerInt = "1";
		else if (speaker.equals("U"))
			speakerInt = "2";
		else
		{
			System.out.println("ERROR: Speaker is unkonw (" + speaker + ")!");
			return;
		}
		
		//System.out.println("Inserting Message with Question ID:" + questionId);
		String conversationId = getConversationIdFromQuestionId(questionId);
		
		String stmt = "INSERT into Message " + 
		" (conversationId, sequence, speaker, content) " +
		"VALUES ( " + conversationId + ", "+ sequence + ", " + speakerInt + ", \'" + content.replaceAll("'", "''").replaceAll("\"", "\"\"") + "\');";
		
		if (connection == null)
		{
			log.info("Connection failed.");
		}
		try 
		{
			connection.createStatement().execute(stmt);
		}
		catch (Exception e)
		{
			log.info("Got an exception with " + stmt, e);
		}
	}
	
	
	
	class Date {
		String year;
		String month;
		String date;
		String hour;
		String minute;
		String second;
		
		public Date (String dateString)
		{
			String fields[] = dateString.split("/| ");
			if (fields.length < 4)
			{
				return;
			}
			else
			{
				if (fields[0].length() == 1)					
					month = "0" + fields[0];
				else 
					month = fields[0];
				
				if (fields[1].length() == 1)					
					date = "0" + fields[1];
				else 
					date = fields[1];
				
				year = fields[2];
				
				String timeFields[] = fields[3].split(":");
				
				if (timeFields.length < 2)
					return;
				else
				{
					if (timeFields[0].length() == 1)					
						hour = "0" + timeFields[0]; 
					else 	
						hour = timeFields[0]; 
					
					if (timeFields[1].length() == 1)					
						minute = "0" + timeFields[1];
					else 	
						minute = timeFields[1];
				
					second = "00";				
				}
			}
		}
		public String toString()
		{
			return year + "-" + month + "-" + date  + " " + hour + ":" + minute + ":" + second;
			
		}
	}
}


